# encoding:gbk
import pandas as pd
import logging
import sys
from datetime import datetime
import sqlite3

class SQLiteDB:
    def __init__(self, db_name):
        """初始化数据库连接"""
        self.db_name = db_name
        self.conn = None
        self.cursor = None

    def connect(self):
        """连接到SQLite数据库"""
        self.conn = sqlite3.connect(self.db_name)
        self.cursor = self.conn.cursor()
        print(f"Connected to {self.db_name} database.")

    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.commit()
            self.conn.close()
            print(f"Connection to {self.db_name} closed.")

    def create_table(self, table_name, columns):
        """创建表格，传入表名和列的定义"""
        columns_str = ', '.join([f"{col} {dtype}" for col, dtype in columns.items()])
        query = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns_str});"
        self.cursor.execute(query)
        print(f"Table '{table_name}' created or already exists.")

    def insert_data(self, table_name, data):
        """插入数据，data是一个字典形式的键值对"""
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['?' for _ in data])
        query = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
        self.cursor.execute(query, tuple(data.values()))
        print(f"Data inserted into '{table_name}'.")

    def fetch_all(self, query, params=()):
        """执行查询，返回所有结果"""
        self.cursor.execute(query, params)
        return self.cursor.fetchall()

    def fetch_one(self, query, params=()):
        """执行查询，返回一个结果"""
        self.cursor.execute(query, params)
        return self.cursor.fetchone()

    def update_data(self, table_name, data, condition):
        """更新数据，data是一个字典，condition是更新条件"""
        set_clause = ', '.join([f"{key} = ?" for key in data])
        query = f"UPDATE {table_name} SET {set_clause} WHERE {condition}"
        self.cursor.execute(query, tuple(data.values()))
        print(f"Data in '{table_name}' updated.")

    def delete_data(self, table_name, condition):
        """删除数据，condition是删除条件"""
        query = f"DELETE FROM {table_name} WHERE {condition}"
        self.cursor.execute(query)
        print(f"Data from '{table_name}' deleted.")

def init(ContextInfo):
    """初始化上下文参数"""
    ContextInfo.accID = 'xxxxxxxx'
    ContextInfo.stock_symbol = '513060.SH'
    ContextInfo.db_file = r'C:\logs\qmt.sqlite'
    # ContextInfo.trace_price = 0
    ContextInfo.threshold = 300
    ContextInfo.turning_point_threshold = 0.002  # 
    ContextInfo.drop_threshold = 0.995  # 下跌0.5%买入
    ContextInfo.up_threshold = 1.005  # 上涨0.5%卖出
    ContextInfo.start = '20250227 09:30:00'
    ContextInfo.end = '20250228 15:00:00'
    # 设置日志
    # logging.basicConfig(filename=ContextInfo.log_file, level=logging.INFO, format='%(asctime)s %(message)s')
    ContextInfo.base_price = get_base_price(ContextInfo)
    ContextInfo.net_grid_data = get_net_grid_data(ContextInfo)

def get_base_price(ContextInfo):
    db = SQLiteDB(ContextInfo.db_file)
    db.connect()
    query = "SELECT price FROM base_price where stock = '513060.SH'"
    result = db.fetch_one(query)
    # print(float(result[0]))
    db.close()
    return float(result[0])

def update_base_price(ContextInfo, price):
    # 更新数据
    db = SQLiteDB(ContextInfo.db_file)
    db.connect()
    update_data = {'price': price}
    condition = "stock = '513060.SH'"
    db.update_data('base_price', update_data, condition)
    db.close()

def get_net_grid_data(ContextInfo):
    db = SQLiteDB(ContextInfo.db_file)
    db.connect()
    query = "SELECT * FROM '513060.SH'"
    result = db.fetch_all(query)
    db.close()
    return result

def turning_point_trigger(ContextInfo):
    bar_time = timetag_to_datetime(ContextInfo.get_bar_timetag(ContextInfo.barpos), '%Y%m%d%H%M%S')
    tick_data = ContextInfo.get_full_tick([ContextInfo.stock_symbol])
    current_price = round(tick_data[ContextInfo.stock_symbol]['lastPrice'],3)
    # logging.info(f"拐点测试：标的：{ContextInfo.stock_symbol}  测试时间:{bar_time}")
    # logging.info(f"--行情推送：最新价：{current_price}/基准价：{ContextInfo.base_price}={current_price/ContextInfo.base_price}  最高价：{ContextInfo.max_price}  最低价：{ContextInfo.min_price}")
    # drop_flag = round((current_price/ContextInfo.base_price), 3) < ContextInfo.drop_threshold
    # logging.info(f"是否进入下跌行情：{drop_flag}")
    # up_flag = round((current_price/ContextInfo.base_price), 3) > ContextInfo.up_threshold
    # logging.info(f"是否进入下跌行情：{up_flag}")
    # ----------------------下跌行情--------------------------- #
    if round((current_price/ContextInfo.base_price), 3) < ContextInfo.drop_threshold:
        # logging.info(f"--下跌行情：最新价：{current_price}  基准价：{ContextInfo.base_price}  时间：{bar_time}")
        # if not ContextInfo.drop_flag:
            # ContextInfo.drop_flag = True
            # ContextInfo.min_price = current_price
            # logging.info(f"--第一次进入下跌行情， 最低价：{ContextInfo.min_price}")
        if current_price < ContextInfo.min_price:
            ContextInfo.min_price = current_price
            print(f"--下跌行情：持续下跌， 更新最低价：{ContextInfo.min_price}")
        elif current_price > ContextInfo.min_price * (1 + ContextInfo.turning_point_threshold):
            logging.info(f"--下跌行情：下跌反弹，最新价:{current_price} > 最低价:{ContextInfo.min_price} * {ContextInfo.turning_point_threshold}")
            execute_trade(ContextInfo, current_price)  # 调用交易函数
            ContextInfo.min_price = current_price
            ContextInfo.max_price = current_price
            ContextInfo.base_price = current_price
    # ----------------------上涨行情--------------------------- #
    if round((current_price/ContextInfo.base_price), 3) > ContextInfo.up_threshold:
        # logging.info(f"最新价：{current_price}  基准价：{ContextInfo.base_price}  时间：{bar_time}")
        # if not ContextInfo.drop_flag:
            # ContextInfo.drop_flag = True
            # ContextInfo.min_price = current_price
            # logging.info(f"--第一次进入下跌行情， 最低价：{ContextInfo.min_price}")
        if current_price > ContextInfo.max_price:
            ContextInfo.max_price = current_price
            logging.info(f"--上涨行情：持续上涨， 更新最高价：{ContextInfo.max_price}")
        elif current_price < ContextInfo.max_price * (1 - ContextInfo.turning_point_threshold):
            logging.info(f"--上涨行情：上涨跳水，最新价:{current_price} < 最高价:{ContextInfo.max_price} * {ContextInfo.turning_point_threshold}")
            execute_trade(ContextInfo, current_price)  # 调用交易函数
            ContextInfo.min_price = current_price
            ContextInfo.max_price = current_price
            ContextInfo.base_price = current_price

def execute_trade(ContextInfo, current_price):
    """处理交易逻辑"""
    # excel_row = ContextInfo.df_price.iloc[(ContextInfo.df_price['price'] - current_price).abs().argsort()[0]]
    # excel_quantity = excel_row['quantity']
    excel_quantity = [item[1] for item in ContextInfo.net_grid_data if item[0] == current_price][0]
    # position_data = get_position(ContextInfo, ContextInfo.accID, 'POSITION')
    position_data = get_holdings(ContextInfo.accID, 'STOCK')
    current_quantity = position_data[ContextInfo.stock_symbol]['可用余额']
    print(f"-- 交易流程：当前持仓量 {current_quantity}  表格持仓量 {excel_quantity}")
    # 表格持仓量 > 当前持仓量，买入
    if excel_quantity > current_quantity and excel_quantity - current_quantity > ContextInfo.threshold:
        account_info = get_account(ContextInfo, ContextInfo.accID, 'STOCK')
        available_cash = account_info.get('可用金额', 0)
        trade_cost = current_price * (excel_quantity - current_quantity)
        if available_cash >= trade_cost:
            tick_data = ContextInfo.get_full_tick([ContextInfo.stock_symbol])
            stk_price = tick_data[ContextInfo.stock_symbol]['askPrice'][1]
            stk_amount = round((excel_quantity - current_quantity)/100) * 100
            passorder(23, 1101, ContextInfo.accID, ContextInfo.stock_symbol, 11, stk_price, stk_amount, 1, ContextInfo)
            # tick_data = ContextInfo.get_full_tick([ContextInfo.stock_symbol])
            # 更新基准价
            update_base_price(ContextInfo, stk_price)
            print(f"--交易流程--\n买入价格 {stk_price} 买入数量 {stk_amount} (Excel {excel_quantity} - 持仓 {current_quantity})")
        else:
            print(f"--交易流程--\n买入失败：可用资金 {available_cash} 不足，需 {trade_cost}")
    # 表格持仓量 < 当前持仓量，卖出
    if excel_quantity < current_quantity and current_quantity - excel_quantity > ContextInfo.threshold:
        tick_data = ContextInfo.get_full_tick([ContextInfo.stock_symbol])
        stk_price = tick_data[ContextInfo.stock_symbol]['bidPrice'][1]
        stk_amount = round((current_quantity - excel_quantity)/100) * 100
        passorder(24, 1101, ContextInfo.accID, ContextInfo.stock_symbol, 11, stk_price, stk_amount, 1, ContextInfo)
        update_base_price(ContextInfo, stk_price)
        print(f"--交易流程--\n卖出价格 {stk_price} 卖出数量 {stk_amount} (持仓 {current_quantity} - Excel {excel_quantity})")

def get_account(ContextInfo, accountid, datatype):
    """获取账户数据（占位实现，需替换为实际接口）"""
    accounts = get_trade_detail_data(accountid, datatype, 'account')  # 假设返回账户详情列表
    result = {}
    for dt in accounts:
        result['总资产'] = dt.m_dBalance
        result['净资产'] = dt.m_dAssureAsset
        result['总市值'] = dt.m_dInstrumentValue
        result['总负债'] = dt.m_dTotalDebit
        result['可用金额'] = dt.m_dAvailable
        result['盈亏'] = dt.m_dPositionProfit
    return result

def get_holdings(accountid, datatype):
    '''
    arg:
        accountid: 账户id, 
        datatype:
            'FUTURE':期货
            'STOCK':股票
            ......
    return:
    {股票名:{'手数':int, '持仓成本':float, '浮动盈亏':float, '可用余额':int}}
    '''
    PositionInfo_dict = {}
    resultlist = get_trade_detail_data(accountid, datatype, 'POSITION')
    for obj in resultlist:
        PositionInfo_dict[obj.m_strInstrumentID + '.' + obj.m_strExchangeID] = {
            '手数':obj.m_nVolume/100,
            '持仓成本':obj.m_dOpenPrice,
            '浮动盈亏':obj.m_dFloatProfit,
            '可用余额':obj.m_nCanUseVolume,
            '成交日期':obj.m_strOpenDate
        }
    return PositionInfo_dict

def handlebar(ContextInfo):
    test(ContextInfo)
    # turning_point_trigger(ContextInfo)
    

def test(ContextInfo):
    bar_time = timetag_to_datetime(ContextInfo.get_bar_timetag(ContextInfo.barpos), '%Y%m%d')
    bar_time1 = timetag_to_datetime(ContextInfo.get_bar_timetag(ContextInfo.barpos), '%Y%m%d%H%M%S')
    
    # print(ContextInfo.base_price)
    # update_base_price(ContextInfo, 0.456)
    # d = get_net_grid_data(ContextInfo)
    # print(d)
    p = 0.426
    # result = [item for item in d if item[0] == p]
    result = [item[1] for item in ContextInfo.net_grid_data if item[0] == p][0]
    print(result)
    print(type(result))
    # print(type(result[0]))


